package com.gaussian.property.module.quartz.task;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.gaussian.property.module.biz.constant.ReportTypeConstants;
import com.gaussian.property.module.biz.entity.*;
import com.gaussian.property.module.biz.mapper.CheckTableMapper;
import com.gaussian.property.module.biz.service.*;
import com.gaussian.property.module.biz.vo.oss.OssConfig;
import com.gaussian.property.module.biz.vo.oss.OssReqeust;
import com.gaussian.property.module.biz.vo.projectreport.AddProjectReport;
import com.songqi.common.core.constant.Constants;
import com.songqi.common.core.constant.HttpStatus;
import com.songqi.common.core.domain.R;
import com.songqi.common.core.exception.CustomException;
import com.songqi.common.core.utils.sign.ShaUtil;
import com.songqi.common.mybatis.core.util.MyBatisUtils;
import com.songqi.common.redis.RedisUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.io.*;
import java.security.NoSuchAlgorithmException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.YearMonth;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @description:
 * @author: songqi(13776346982)
 * @date: 2023/3/16 20:53
 */
@Slf4j
@Component("bizTask")
public class BizTask {

    @Resource
    private IProjectReportFileService iProjectReportFileService;
    @Resource
    private IProjectCheckStandardScoreService iProjectCheckStandardScoreService;
    @Resource
    private IProjectService iProjectService;
    @Resource
    private IProjectCheckRecordService iProjectCheckRecordService;
    @Resource
    private CheckTableMapper checkTableMapper;
    @Resource
    private IDailyStatisticsService iDailyStatisticsService;
    @Resource
    private ICheckModuleService iCheckModuleService;
    @Resource
    private ICheckCriteriaService iCheckCriteriaService;
    @Resource
    private ICheckStandardService iCheckStandardService;
    @Resource
    private IProjectCheckTableService iProjectCheckTableService;
    @Resource
    private ICheckTableService iCheckTableService;

    @Resource
    private RedisUtils redisUtils;

    @Value("${gaussian.api.privateSign.url}")
    private String url;

    @Value("${gaussian.api.privateSign.appKey}")
    private String appKey;

    @Value("${gaussian.api.privateSign.appSecret}")
    private String appSecret;

    public OssConfig getOssConfig() throws NoSuchAlgorithmException {
        String ossConfigKey = "gaussian.ossConfig";
        OssConfig ossConfig = redisUtils.getCacheObject(ossConfigKey);
        if (ossConfig != null) {
            return ossConfig;
        }
        long timestamp = System.currentTimeMillis() / 1000;
        String fieldStr = appKey + appSecret + timestamp;
        String hashSign = ShaUtil.getSha256HexString(fieldStr);
        OssReqeust ossReqeust = new OssReqeust();
        ossReqeust.setAppKey(appKey);
        ossReqeust.setTimestamp(timestamp + "");
        ossReqeust.setSign(hashSign);

        String body = JSONUtil.toJsonStr(ossReqeust);
        String frontRequestId = "property-1.0.0-9G3EjnLD" + System.currentTimeMillis();
        log.info("加密参数appKey={}, timestamp={}, fieldStr={}, sign={}, frontRequestId={} 调用高仙{} 请求参数：body={}",
                appKey, timestamp, fieldStr, hashSign, frontRequestId, url, body);
        HttpResponse response = HttpRequest.post(url).header("front-request-id", frontRequestId).body(body).execute();
        if (response.isOk()) {
            String respBody = response.body();
            log.info("调用高仙{}返回结果：{}", url, respBody);
            R r = JSONUtil.toBean(respBody, R.class);
            if (r.getCode() == HttpStatus.SUCCESS) {
                // 解析数据
                JSONObject jsonObject = (JSONObject) r.getData();
                ossConfig = jsonObject.toBean(OssConfig.class);
                String expiration = ossConfig.getExpiration();
                DateTime dateTime = DateUtil.parse(expiration).setTimeZone(TimeZone.getTimeZone("Asia/Shanghai"));
                long diff = DateUtil.between(new Date(), dateTime, DateUnit.SECOND);
                redisUtils.setCacheObject(ossConfigKey, ossConfig, diff - 5, TimeUnit.SECONDS);
            } else {
                log.error("调用高仙{}失败。失败信息：{}", url, r.getMsg());
                throw new CustomException("查询oss参数失败。错误原因：" + r.getMsg());
            }
        } else {
            log.error("连接超时");
            throw new CustomException(String.format("调用高仙接口%s发生异常", url));
        }

        return ossConfig;
    }


    /**
     * 生成月底稽核报表。每月1号生成上一个月的数据。
     */
    public void createMonthlyCheckReport(String month) {
        log.info("开始生成月稽核报表");
        LocalDate endData = LocalDate.now().plusDays(-1);
        LocalDate beginDate = endData.withDayOfMonth(1);
        if(CharSequenceUtil.isNotBlank(month)) {
            // 将输入的日期字符串解析为YearMonth对象
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMM");
            YearMonth yearMonth = YearMonth.parse(month, formatter);
            // 获取当月的第一天
            beginDate = yearMonth.atDay(1);
            // 获取当月的最后一天
            endData = yearMonth.atEndOfMonth();
        }
        log.info("开始生成月稽核报表。开始时间{}-{}", beginDate, endData);
        // 查询所有荐项目id
        List<String> projectIdList = getAllProjectIdList();
        // 查询本月第一天到今天的所有稽核记录
        List<ProjectCheckStandardScore> projectCheckStandardScoreList = getProjectCheckStandardScoreList(beginDate, endData);
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            List<String> hasCheckStandardScoreProjectIdList = projectCheckStandardScoreList.stream().map(ProjectCheckStandardScore::getProjectId).distinct().collect(Collectors.toList());
            // 移除有稽核记录的项目id
            projectIdList.removeAll(hasCheckStandardScoreProjectIdList);
            //将稽核记录转换成Map<projectId, Map<checkTableId, ProjectCheckStandardScore>>数据格式
            Map<String, Map<String, List<ProjectCheckStandardScore>>> projectCheckStandardScoreMap = getProjectCheckStandardScoreMap(projectCheckStandardScoreList);
            // 遍历有稽核记录的项目id
            for (Map.Entry<String, Map<String, List<ProjectCheckStandardScore>>> entry : projectCheckStandardScoreMap.entrySet()) {
                createProjectReportHasCheckRecord(entry, beginDate, endData);
            }
        }
        // 生成没有稽核记录的项目的稽核报表
        if (CollUtil.isNotEmpty(projectIdList)) {
            for (String projectId : projectIdList) {
                List<String> checkTableIdList = getCheckTableIdList(projectId);
                if (CollUtil.isEmpty(checkTableIdList)) {
                    continue;
                }
                createProjectReportWithoutCheckRecord(projectId, checkTableIdList, beginDate, endData);
            }
        }
    }


    /**
     * 创建有稽核记录的项目的报表
     * entry数据格式：Map<projectId, Map<checkTableId, ProjectCheckStandardScore>> entry
     *
     * @param entry
     * @param beginDate
     * @param today
     */
    private void createProjectReportHasCheckRecord(Map.Entry<String, Map<String, List<ProjectCheckStandardScore>>> entry, LocalDate beginDate, LocalDate today) {
        // 项目id
        String projectId = entry.getKey();
        // Map<checkTableId, List<ProjectCheckStandardScore>>
        Map<String, List<ProjectCheckStandardScore>> checkStandardScoreMap = entry.getValue();
        for (Map.Entry<String, List<ProjectCheckStandardScore>> checkStandardScoreEntry : checkStandardScoreMap.entrySet()) {
            // 按项目id + 稽核表Id + 月份 生成一个报表文件
            String month = String.format("%d%02d", today.getYear(), today.getMonthValue());
            Project project = iProjectService.getById(projectId);
            String checkTableId = checkStandardScoreEntry.getKey();
            try {
                // 只查询未删除的checkTableId
                CheckTable checkTable = checkTableMapper.selectById(checkTableId);
                ProjectCheckTable projectCheckTable = iProjectCheckTableService.queryProjectCheckTable(project.getProjectId(), checkTableId);
                // 只生成启用的项目稽核表
                if (projectCheckTable != null && projectCheckTable.getStatus().equals(Constants.ENABLE)) {
                    String fileName = project.getProjectName() + checkTable.getCheckName() + "-" + month + ".xlsx";
                    if (!iProjectReportFileService.checkReportIsExist(projectId, month, ReportTypeConstants.CHECK, fileName)) {
                        List<ProjectCheckStandardScore> projectCheckStandardScoreList = checkStandardScoreEntry.getValue();
                        AddProjectReport addProjectReport = createWorkbookAndUploadOss(project, checkTable, projectCheckStandardScoreList, month, beginDate, today);
                        // 上传报告
                        iProjectReportFileService.addProjectReport(addProjectReport);
                    }
                }
            } catch (Exception e) {
                log.error("创建报表失败。项目名：{}， 稽核表Id：{}， 月份：{}。失败原因：{}", projectId, checkTableId, today, e.getMessage(), e);
            }
        }
    }

    /**
     * 创建没有稽核记录的项目的报表
     *
     * @param projectId
     * @param checkTableIdList
     * @param beginDate
     * @param today
     */
    private void createProjectReportWithoutCheckRecord(String projectId, List<String> checkTableIdList, LocalDate beginDate, LocalDate today) {
        for (String checkTableId : checkTableIdList) {
            // 按项目id + 稽核表Id + 月份 生成一个报表文件
            try {
                String month = String.format("%d%02d", today.getYear(), today.getMonthValue());
                Project project = iProjectService.getById(projectId);
                // 只查询未删除的checkTable
                CheckTable checkTable = checkTableMapper.selectById(checkTableId);
                if (project == null || checkTable == null) {
                    continue;
                }
                String fileName = project.getProjectName() + checkTable.getCheckName() + "-" + month + ".xlsx";
                // 生成报告前，先判断之前有没有生成过
                if (!iProjectReportFileService.checkReportIsExist(projectId, month, ReportTypeConstants.CHECK, fileName)) {
                    AddProjectReport addProjectReport = createWorkbookAndUploadOss(project, checkTable, Collections.emptyList(), month, beginDate, today);
                    // 上传报告
                    iProjectReportFileService.addProjectReport(addProjectReport);
                }

            } catch (Exception e) {
                e.printStackTrace();
                log.error("创建报表失败。项目名：{}， 稽核表Id：{}， 月份：{}。失败原因：{}", projectId, checkTableId, today, e.getMessage());
            }
        }
    }


    /**
     * 查询项目配置的稽核表
     *
     * @param projectId
     * @return
     */
    private List<String> getCheckTableIdList(String projectId) {
        LambdaQueryWrapper<ProjectCheckTable> queryWrapper = new LambdaQueryWrapper<ProjectCheckTable>()
                .eq(ProjectCheckTable::getProjectId, projectId)
                .eq(ProjectCheckTable::getStatus, Constants.ENABLE);
        List<ProjectCheckTable> list = iProjectCheckTableService.list(queryWrapper);
        if (CollUtil.isNotEmpty(list)) {
            return list.stream().map(ProjectCheckTable::getCheckTableId).collect(Collectors.toList());
        }
        return Collections.emptyList();
    }

    private List<String> getAllProjectIdList() {

        List<Project> list = iProjectService.list();
        if (CollUtil.isNotEmpty(list)) {
            return list.stream().map(Project::getProjectId).collect(Collectors.toList());
        }
        return Collections.emptyList();
    }

    /**
     * 按项目id + 稽核表Id + 月份 生成一个报表文件， 并上传至oss
     *
     * @param project
     * @param checkTable
     * @param projectCheckStandardScoreList
     * @param month
     * @param beginDate
     * @param today
     * @return
     * @throws IOException
     */
    private AddProjectReport createWorkbookAndUploadOss(Project project, CheckTable checkTable, List<ProjectCheckStandardScore> projectCheckStandardScoreList, String month, LocalDate beginDate, LocalDate today) throws IOException {
        if (project != null && checkTable != null) {
            String fileName = project.getProjectName() + checkTable.getCheckName() + "-" + month + ".xlsx";
            // 创建报表工作表
            try (Workbook workbook = createReportWorkbook(project, checkTable, projectCheckStandardScoreList, beginDate, today);) {
//                createFile(fileName, workbook);
                // 创建文件并上传至oss，返回oss保存路径
                String objectName = "property/xlsx/" + fileName;
                if (uploadOssFile(objectName, workbook)) {
                    AddProjectReport.ReportFile reportFile = new AddProjectReport.ReportFile();
                    reportFile.setFileName(fileName);
                    reportFile.setPath(objectName);
                    AddProjectReport addProjectReport = new AddProjectReport();
                    addProjectReport.setReportType(ReportTypeConstants.CHECK);
                    addProjectReport.setMonth(month);
                    addProjectReport.setProjectIdList(Collections.singletonList(project.getProjectId()));
                    addProjectReport.setFile(reportFile);
                    return addProjectReport;
                } else {
                    throw new CustomException("文件上传oss失败。");
                }
            } catch (Exception e) {
                log.error("生成报表文件失败。失败原因：{}", e.getMessage(), e);
            }
        }
        return null;
    }


    /**
     * 创建工作表。
     *
     * @param project
     * @param checkTable
     * @param projectCheckStandardScoreList
     * @param beginDate
     * @param endDate
     * @return
     */
    private Workbook createReportWorkbook(Project project, CheckTable checkTable, List<ProjectCheckStandardScore> projectCheckStandardScoreList, LocalDate beginDate, LocalDate endDate) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(project.getProjectName());

        // 行号
        int rowNum = 0;
        createTitleRow(sheet, rowNum++, checkTable, beginDate, endDate);
        createHeaderRow(sheet, rowNum++, beginDate, endDate);
        createAttendanceRow(sheet, rowNum++, project.getProjectId(), beginDate, endDate);
        rowNum = createDataListRow(sheet, rowNum, checkTable, projectCheckStandardScoreList, beginDate, endDate);
        // 扣分合计行
        createTotalDeduction(sheet, rowNum++, projectCheckStandardScoreList, beginDate, endDate);
        // 得分行
        createScoreRow(sheet, rowNum++, checkTable, projectCheckStandardScoreList, beginDate, endDate);
        double passingScore = getPassingScore(project, checkTable);
        createPassingScoreRow(sheet, rowNum++, passingScore, beginDate, endDate);
        createPassingResultRow(sheet, rowNum++, passingScore, beginDate, endDate);
        createCheckRemarkRow(sheet, rowNum++, project.getProjectId(), checkTable.getCheckTableId(), beginDate, endDate);
        String systemEvaluation = getSystemEvaluation(projectCheckStandardScoreList);
        createSystemEvaluationRow(sheet, rowNum, systemEvaluation, beginDate, endDate);
        return workbook;
    }


    private double getPassingScore(Project project, CheckTable checkTable) {
        double passingScore = 0d;
        ProjectCheckTable projectCheckTable = iProjectCheckTableService.queryProjectCheckTable(project.getProjectId(), checkTable.getCheckTableId());
        if (projectCheckTable != null) {
            passingScore = projectCheckTable.getPassingScore();
        }
        return passingScore;
    }

    private String createFile(String fileName, Workbook workbook) throws IOException {
        String filePath = "/Users/songqi/Desktop/checkreport/" + fileName;
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        // 保存 Excel 文件
        try (FileOutputStream fileOut = new FileOutputStream(file)) {
            workbook.write(fileOut);
        }
        return filePath;
    }


    private boolean uploadOssFile(String objectName, Workbook workbook) throws IOException, NoSuchAlgorithmException {
        OssConfig ossConfig = getOssConfig();
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            // 将 Workbook 写入到 ByteArrayOutputStream
            workbook.write(outputStream);
            // 获取字节数组
            byte[] byteArray = outputStream.toByteArray();
            // 创建 InputStream
            ByteArrayInputStream inputStream = new ByteArrayInputStream(byteArray);
            return ossConfig.uploadFile(inputStream, objectName);
        } finally {
            // 确保最后关闭 workbook
            workbook.close();
        }

    }


    /**
     * 创建标题行 第1行
     *
     * @param sheet
     * @param rowNum
     * @param checkTable
     * @param beginDate
     * @param endDate
     */
    private static void createTitleRow(Sheet sheet, int rowNum, CheckTable checkTable, LocalDate beginDate, LocalDate endDate) {
        long daysBetween = ChronoUnit.DAYS.between(beginDate, endDate);
        Row titleRow = sheet.createRow(rowNum);
        titleRow.createCell(0).setCellValue(endDate.format(DateTimeFormatter.ofPattern("yyyy年MM月份")) + checkTable.getCheckName() + "每日现场检查考核表");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (int) (4 + daysBetween)));
    }

    /**
     * 创建表头行
     *
     * @param sheet
     * @param rowNum
     * @param beginDate
     * @param endDate
     */
    private void createHeaderRow(Sheet sheet, int rowNum, LocalDate beginDate, LocalDate endDate) {
        Row headerRow = sheet.createRow(rowNum);
        headerRow.createCell(0).setCellValue("模块");
        headerRow.createCell(1).setCellValue("指标");
        headerRow.createCell(2).setCellValue("标准");
        headerRow.createCell(3).setCellValue("扣分标准");
        int i = 4;
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("M月dd日");
        for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
            headerRow.createCell(i++).setCellValue(date.format(formatter));
        }
    }

    /**
     * 创建数据行
     *
     * @param sheet
     * @param rowNum
     * @param checkTable
     * @param projectCheckStandardScoreList
     * @param beginDate
     * @param endDate
     * @return
     */
    private int createDataListRow(Sheet sheet, int rowNum, CheckTable checkTable, List<ProjectCheckStandardScore> projectCheckStandardScoreList, LocalDate beginDate, LocalDate endDate) {
        Map<String, List<ProjectCheckStandardScore>> checkStandardIdMap = null;
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            checkStandardIdMap = projectCheckStandardScoreList.stream()
                    .collect(Collectors.groupingBy(ProjectCheckStandardScore::getCheckStandardId));
        }
        List<CheckModule> checkModuleList = getCheckModuleList(checkTable.getCheckTableId());
        if (CollUtil.isEmpty(checkModuleList)) {
            return rowNum;
        }
        int moduleMergeStart = 0;
        for (CheckModule checkModule : checkModuleList) {
            int moduleMergerRowCount = 0;
            List<CheckCriteria> checkCriteriaList = getCheckCriteriaList(checkModule.getCheckModuleId());
            if (CollUtil.isEmpty(checkCriteriaList)) {
                continue;
            }
            moduleMergeStart = rowNum;
            int criteriaMergeStart = 0;
            for (CheckCriteria checkCriteria : checkCriteriaList) {
                criteriaMergeStart = rowNum;
                List<CheckStandard> checkStandardList = getCheckStandardList(checkCriteria.getCheckCriteriaId());
                if (CollUtil.isEmpty(checkStandardList)) {
                    continue;
                }
                rowNum = createCheckStandardRow(sheet, rowNum, beginDate, endDate, checkModule, checkCriteria, checkStandardList, checkStandardIdMap);
                // 合并指标列
                if (checkStandardList.size() > 1) {
                    sheet.addMergedRegion(new CellRangeAddress(criteriaMergeStart, criteriaMergeStart - 1 + checkStandardList.size(), 1, 1));
                }
                moduleMergerRowCount += checkStandardList.size();
            }
            // 合并模块列
            if (moduleMergerRowCount > 1) {
                sheet.addMergedRegion(new CellRangeAddress(moduleMergeStart, moduleMergeStart - 1 + moduleMergerRowCount, 0, 0));
            }
        }
        return rowNum;
    }

    /**
     * 创建出勤人数行
     *
     * @param sheet
     * @param rowNum
     * @param projectId
     * @param beginDate
     * @param endDate
     */
    private void createAttendanceRow(Sheet sheet, int rowNum, String projectId, LocalDate beginDate, LocalDate endDate) {
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("出勤人数");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
        // 创建出勤数据列
        Map<LocalDate, List<DailyStatistics>> dateListMap = new LinkedHashMap<>();
        List<DailyStatistics> dailyStatisticsList = iDailyStatisticsService.getDailyStatisticsList(projectId, beginDate, endDate);
        if (CollUtil.isNotEmpty(dailyStatisticsList)) {
            dateListMap = dailyStatisticsList.stream().collect(Collectors.groupingBy(DailyStatistics::getDate));
        }
        // 数据从第5列开始
        int i = 4;
        for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
            row.createCell(i++).setCellValue(getAttendanceCount(dateListMap, date));
        }
    }

    /**
     * 扣分总计行
     *
     * @param sheet
     * @param rowNum
     * @param projectCheckStandardScoreList
     * @param beginDate
     * @param endDate
     */
    private void createTotalDeduction(Sheet sheet, int rowNum, List<ProjectCheckStandardScore> projectCheckStandardScoreList, LocalDate beginDate, LocalDate endDate) {
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("扣分合计");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            int i = 4;
            Map<LocalDate, List<ProjectCheckStandardScore>> dateProjectCheckStandardScoreListMap = projectCheckStandardScoreList.stream().collect(Collectors.groupingBy(t -> DateUtil.toLocalDateTime(t.getCreateTime()).toLocalDate()));
            for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
                row.createCell(i++).setCellValue(getTotalDeduction(dateProjectCheckStandardScoreListMap, date));
            }
        }
    }

    /**
     * 总得分行
     * 总得分= 稽核表的所有扣分模块设置的分值  + 当天所有标准得分（有正有负）
     *
     * @param sheet
     * @param rowNum
     * @param checkTable
     * @param projectCheckStandardScoreList
     * @param beginDate
     * @param endDate
     */
    private void createScoreRow(Sheet sheet, int rowNum, CheckTable checkTable, List<ProjectCheckStandardScore> projectCheckStandardScoreList, LocalDate beginDate, LocalDate endDate) {
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("得分");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));

        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            int i = 4;
            Map<LocalDate, List<ProjectCheckStandardScore>> dateProjectCheckStandardScoreListMap = projectCheckStandardScoreList.stream().collect(Collectors.groupingBy(t -> DateUtil.toLocalDateTime(t.getCreateTime()).toLocalDate()));
            // 稽核表的所有扣分模块设置的分值
            double minusModuleScoreInCheckTable = iCheckTableService.queryTotalMinusModuleScoreInCheckTable(checkTable.getCheckTableId());
            for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
                // 区间内所有标准得分(有正有负)
                List<ProjectCheckStandardScore> dateProjectCheckStandardScoreList = dateProjectCheckStandardScoreListMap.get(date);
                if (CollUtil.isNotEmpty(dateProjectCheckStandardScoreList)) {
                    double totalStandardDeduction = dateProjectCheckStandardScoreList.stream().mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
                    row.createCell(i++).setCellValue(minusModuleScoreInCheckTable + totalStandardDeduction);
                } else {
                    row.createCell(i++).setCellValue("");
                }
            }
        }
    }

    /**
     * 合格标准
     *
     * @param sheet
     * @param rowNum
     * @param passingScore
     * @param beginDate
     * @param endDate
     */
    private void createPassingScoreRow(Sheet sheet, int rowNum, double passingScore, LocalDate beginDate, LocalDate endDate) {
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("合格标准");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
        int i = 4;
        for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
            row.createCell(i++).setCellValue(passingScore);
        }
    }

    private void createCheckRemarkRow(Sheet sheet, int rowNum, String projectId, String checkTableId, LocalDate beginDate, LocalDate endDate) {
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("稽核补充说明");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
        Map<LocalDate, List<ProjectCheckRecord>> projectCheckRecordByDate = getProjectCheckRecordByDate(projectId, checkTableId, beginDate, endDate);
        int i = 4;
        for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
            String val = "";
            List<ProjectCheckRecord> projectCheckRecords = projectCheckRecordByDate.get(date);
            if (CollUtil.isNotEmpty(projectCheckRecords)) {
                val = projectCheckRecords.stream().map(ProjectCheckRecord::getRemark).collect(Collectors.joining(","));
            }
            row.createCell(i++).setCellValue(val);
        }
    }


    private Map<LocalDate, List<ProjectCheckRecord>> getProjectCheckRecordByDate(String projectId, String checkTableId, LocalDate beginDate, LocalDate endDate) {
        Map<LocalDate, List<ProjectCheckRecord>> projectCheckRecordMap = new HashMap<>();
        List<ProjectCheckRecord> projectCheckRecords = iProjectCheckRecordService.queryProjectCheckRecord(projectId, checkTableId, LocalDateTime.of(beginDate, LocalTime.MIN), LocalDateTime.of(endDate, LocalTime.MAX));
        if (CollUtil.isNotEmpty(projectCheckRecords)) {
            List<ProjectCheckRecord> collect = projectCheckRecords.stream().filter(t -> CharSequenceUtil.isNotBlank(t.getRemark())).collect(Collectors.toList());
            if (CollUtil.isNotEmpty(collect)) {
                projectCheckRecordMap = collect.stream().collect(Collectors.groupingBy(t -> DateUtil.toLocalDateTime(t.getCreateTime()).toLocalDate()));
            }
        }
        return projectCheckRecordMap;
    }

    /**
     * 统计各个三级标准的扣分分值，找出三个最高的，显示出来，如果为0不显示
     * 占比是这个三级标准在这个月的扣分➗这个月的总扣分
     *
     * @return
     */
    private String getSystemEvaluation(List<ProjectCheckStandardScore> projectCheckStandardScoreList) {
        // 总扣分的和
        double totalDeduction = projectCheckStandardScoreList.stream().filter(t -> t.getDeduction() !=null && t.getDeduction() < 0).mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
        StringBuilder tips = new StringBuilder();
        // 指标XXX扣分XX分，占总扣分项XXX%，指标XXX扣分XX分，占总扣分项XXX%，指标XXX扣分XX分，占总扣分项XXX%，需加强对应管控。
        StringBuilder tips1Builder = new StringBuilder();
        //指标XXX扣分XX分，占总扣分项XXX%，指标XXX扣分XX分，占总扣分项XXX%，指标XXX扣分XX分，占总扣分项XXX%，需加强对应管控。
        StringBuilder tips2Builder = new StringBuilder();
        Map<String, CheckStandard> checkStandardMap = getCheckStandardMap(projectCheckStandardScoreList);
        // 统计每个标准总得分（加分和扣分的总和）
        List<ProjectCheckStandardScore> mixSumDeductionForCheckStandardList = sumDeductionForEachStandard(projectCheckStandardScoreList, "mix");
        // 取得分是得分小于0的稽核标准
        List<ProjectCheckStandardScore> filterLt0ProjectCheckStandardScoreList = mixSumDeductionForCheckStandardList.stream()
                .filter(t -> t.getDeduction() < 0).collect(Collectors.toList());
        if (CollUtil.isNotEmpty(filterLt0ProjectCheckStandardScoreList)) {
            // 取得分小于0前三的标准
            List<ProjectCheckStandardScore> top3MaxDeductionProjectCheckStandardScoreList = getTopMaxDeductionProjectCheckStandardScores(mixSumDeductionForCheckStandardList, 3);
            tips1Builder.append("扣分项指标最多为");
            for (ProjectCheckStandardScore maxDeductionProjectCheckStandardScore : top3MaxDeductionProjectCheckStandardScoreList) {
                String checkStandardId = maxDeductionProjectCheckStandardScore.getCheckStandardId();
                CheckStandard checkStandard = checkStandardMap.get(checkStandardId);
                if (checkStandard != null) {
                    tips1Builder.append("“").append(checkStandard.getStandardName()).append("”");
                    tips1Builder.append("；");
                    tips2Builder.append("指标")
                            .append("“").append(checkStandard.getStandardName()).append("”")
                            .append("扣分：").append(maxDeductionProjectCheckStandardScore.getDeduction());
                    if(totalDeduction != 0){
                        tips2Builder
                                .append("，占总扣分项的")
                                .append(NumberUtil.formatPercent(NumberUtil.div(maxDeductionProjectCheckStandardScore.getDeduction().doubleValue(), totalDeduction), 2));
                        tips2Builder.append("；");
                    }
                }
            }
            if (CharSequenceUtil.isNotBlank(tips1Builder)) {
                tips2Builder.append("需加强对应管控。");
            }
        }
        if (CharSequenceUtil.isNotBlank(tips2Builder)) {
            tips.append(tips1Builder);
        }
        if (CharSequenceUtil.isNotBlank(tips2Builder)) {
            tips.append("；").append(tips2Builder);
        }
        return tips.toString();
    }

    /**
     * 按扣分值由大到小排序，取前top个最小的（扣分是负值，最小意味着扣分最多）记录.
     *
     * @param maxDeductionCheckStandardScoreList
     * @param top
     * @return
     */
    private static List<ProjectCheckStandardScore> getTopMaxDeductionProjectCheckStandardScores(List<ProjectCheckStandardScore> maxDeductionCheckStandardScoreList, int top) {
        List<ProjectCheckStandardScore> projectCheckStandardScoreSortedByDeduction = maxDeductionCheckStandardScoreList.stream()
                .sorted(Comparator.comparing(ProjectCheckStandardScore::getDeduction))
                .collect(Collectors.toList());
        if (projectCheckStandardScoreSortedByDeduction.size() > top) {
            projectCheckStandardScoreSortedByDeduction = projectCheckStandardScoreSortedByDeduction.subList(0, top);
        }
        return projectCheckStandardScoreSortedByDeduction;
    }


    /**
     * 统计每个3级标准的得分总和。汇总方式：所有扣分和加分之和、所有加分之和、所有扣分之和
     *
     * @param projectCheckStandardScoreList
     * @param sumType                       mix:所有扣分和加分之和;addScore:所有加分之和;minusScore:所有扣分之和
     * @return
     */
    private List<ProjectCheckStandardScore> sumDeductionForEachStandard(List<ProjectCheckStandardScore> projectCheckStandardScoreList, String sumType) {
        List<ProjectCheckStandardScore> maxDeductionCheckStandardScoreList = new ArrayList<>();
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            Map<String, List<ProjectCheckStandardScore>> standardIdMap = projectCheckStandardScoreList.stream()
                    .collect(Collectors.groupingBy(ProjectCheckStandardScore::getCheckStandardId));
            for (Map.Entry<String, List<ProjectCheckStandardScore>> entry : standardIdMap.entrySet()) {
                List<ProjectCheckStandardScore> checkStandardScoreList = entry.getValue();
                if (CollUtil.isNotEmpty(checkStandardScoreList)) {
                    Double deduction = null;
                    // 该3级标准的所有扣分和加分之和
                    if ("mix".equals(sumType)) {
                        deduction = checkStandardScoreList.stream().mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
                    }
                    // 该3级标准的所有加分之和
                    if ("addScore".equals(sumType)) {
                        deduction = checkStandardScoreList.stream().filter(t -> t.getDeduction() > 0).mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
                    }
                    // 该3级标准的所有扣分之和
                    if ("minusScore".equals(sumType)) {
                        deduction = checkStandardScoreList.stream().filter(t -> t.getDeduction() < 0).mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
                    }
                    ProjectCheckStandardScore checkStandardScore = checkStandardScoreList.get(0);
                    checkStandardScore.setDeduction(deduction);
                    maxDeductionCheckStandardScoreList.add(checkStandardScore);
                }
            }
        }
        return maxDeductionCheckStandardScoreList;
    }




    private void createSystemEvaluationRow(Sheet sheet, int rowNum, String systemEvaluation, LocalDate beginDate, LocalDate endDate) {
        long daysBetween = ChronoUnit.DAYS.between(beginDate, endDate);
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("系统建议");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
        row.createCell(4).setCellValue(systemEvaluation);
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 4, (int) (4 + daysBetween)));
    }

    /**
     * 创建是否合格行
     *
     * @param sheet
     * @param rowNum
     * @param passingScore
     * @param beginDate
     * @param endDate
     */
    private void createPassingResultRow(Sheet sheet, int rowNum, double passingScore, LocalDate beginDate, LocalDate endDate) {
        Row scoreRow = sheet.getRow(rowNum - 2);
        Row row = sheet.createRow(rowNum);
        row.createCell(0).setCellValue("是否合格");
        // 合并前4列
        sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 3));
        int i = 4;
        for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
            Cell cell = scoreRow.getCell(i);
            if (cell != null) {
                if (cell.getCellType().equals(CellType.NUMERIC)) {
                    double score = cell.getNumericCellValue();
                    if (passingScore > score) {
                        row.createCell(i).setCellValue("否");
                    } else {
                        row.createCell(i).setCellValue("是");
                    }
                } else if (cell.getCellType().equals(CellType.STRING)) {
                    String scoreStr = cell.getStringCellValue();
                    if (CharSequenceUtil.isNotBlank(scoreStr)) {
                        if (passingScore > Double.parseDouble(scoreStr)) {
                            row.createCell(i).setCellValue("否");
                        } else {
                            row.createCell(i).setCellValue("是");
                        }
                    }
                }
            }
            i++;
        }
    }


    /**
     * 查询某一天所有三级指标的扣分总计
     *
     * @param dateListMap
     * @param date
     * @return
     */
    private double getTotalDeduction(Map<LocalDate, List<ProjectCheckStandardScore>> dateListMap, LocalDate date) {
        List<ProjectCheckStandardScore> projectCheckStandardScoreList = dateListMap.get(date);
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            return projectCheckStandardScoreList.stream().mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
        }
        return 0;
    }


    /**
     * 将标准稽核记录转成map
     *
     * @param projectCheckStandardScoreList
     * @return
     */
    private Map<String, CheckStandard> getCheckStandardMap(List<ProjectCheckStandardScore> projectCheckStandardScoreList) {
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            List<String> checkStandardIdList = projectCheckStandardScoreList.stream().map(ProjectCheckStandardScore::getCheckStandardId).distinct().collect(Collectors.toList());
            LambdaQueryWrapper<CheckStandard> queryWrapper = new LambdaQueryWrapper<>();
            MyBatisUtils.cutInParam(queryWrapper, CheckStandard::getCheckStandardId, checkStandardIdList);
            List<CheckStandard> checkStandardList = iCheckStandardService.list(queryWrapper);
            if (CollUtil.isNotEmpty(checkStandardList)) {
                return checkStandardList.stream().collect(Collectors.toMap(CheckStandard::getCheckStandardId, Function.identity()));
            }
        }
        return Collections.emptyMap();
    }


    private int createCheckStandardRow(Sheet sheet,
                                       int rowNum,
                                       LocalDate beginDate,
                                       LocalDate endDate,
                                       CheckModule checkModule,
                                       CheckCriteria checkCriteria,
                                       List<CheckStandard> checkStandardList,
                                       Map<String, List<ProjectCheckStandardScore>> checkStandardIdWithCheckRecordMap) {
        for (CheckStandard checkStandard : checkStandardList) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(checkModule.getModuleName());
            row.createCell(1).setCellValue(checkCriteria.getCriteriaName());
            row.createCell(2).setCellValue(checkStandard.getStandardName());
            row.createCell(3).setCellValue(checkStandard.getDeductionItem());
            int i = 4;
            for (LocalDate date = beginDate; !date.isEqual(endDate); date = date.plusDays(1)) {
                Double checkStandardDeductionCellVal = getCheckStandardDeductionCellVal(date, checkStandard, checkStandardIdWithCheckRecordMap);
                if (checkStandardDeductionCellVal != null) {
                    row.createCell(i++).setCellValue(checkStandardDeductionCellVal);
                } else {
                    row.createCell(i++).setCellValue("");
                }
            }
        }
        return rowNum;
    }

    private Double getCheckStandardDeductionCellVal(LocalDate date, CheckStandard checkStandard, Map<String, List<ProjectCheckStandardScore>> checkStandardIdMap) {
        if (CollUtil.isNotEmpty(checkStandardIdMap)) {
            List<ProjectCheckStandardScore> projectCheckStandardScores = checkStandardIdMap.get(checkStandard.getCheckStandardId());
            if (projectCheckStandardScores != null) {
                // 该标准有稽核记录，将稽核记录日期是date的放入dateCheckStandardScoreList中
                List<ProjectCheckStandardScore> dateCheckStandardScoreList = new ArrayList<>();
                for (ProjectCheckStandardScore projectCheckStandardScore : projectCheckStandardScores) {
                    if (DateUtil.toLocalDateTime(projectCheckStandardScore.getCreateTime()).toLocalDate().equals(date)) {
                        dateCheckStandardScoreList.add(projectCheckStandardScore);
                    }
                }
                if (CollUtil.isNotEmpty(dateCheckStandardScoreList)) {
                    return dateCheckStandardScoreList.stream().mapToDouble(ProjectCheckStandardScore::getDeduction).sum();
                }
            }
        }
        return null;
    }


    /**
     * 获取每天的出勤人数。如果没有，返回空字符串
     *
     * @param dateListMap
     * @param date
     * @return
     */
    private int getAttendanceCount(Map<LocalDate, List<DailyStatistics>> dateListMap, LocalDate date) {
        List<DailyStatistics> dailyStatistics = dateListMap.get(date);
        if (CollUtil.isNotEmpty(dailyStatistics)) {
            return dailyStatistics.get(0).getClockAttendanceCrewCount();
        }
        return 0;
    }

    /**
     * 将稽核记录转换成Map<projectId, Map<checkTableId, ProjectCheckStandardScore>>数据格式
     */
    private Map<String, Map<String, List<ProjectCheckStandardScore>>> getProjectCheckStandardScoreMap(List<ProjectCheckStandardScore> projectCheckStandardScoreList) {
        Map<String, Map<String, List<ProjectCheckStandardScore>>> projectIdMap = new HashMap<>();
        if (CollUtil.isNotEmpty(projectCheckStandardScoreList)) {
            Map<String, List<ProjectCheckStandardScore>> collect = projectCheckStandardScoreList.stream().collect(Collectors.groupingBy(ProjectCheckStandardScore::getProjectId));
            for (Map.Entry<String, List<ProjectCheckStandardScore>> entry : collect.entrySet()) {
                String projectId = entry.getKey();
                List<ProjectCheckStandardScore> checkStandardScoreList = entry.getValue();
                Map<String, List<ProjectCheckStandardScore>> checkTableMap = checkStandardScoreList.stream().collect(Collectors.groupingBy(ProjectCheckStandardScore::getCheckTableId));
                projectIdMap.put(projectId, checkTableMap);
            }
        }
        return projectIdMap;
    }

    private List<ProjectCheckStandardScore> getProjectCheckStandardScoreList(LocalDate firstDayOfMonth, LocalDate today) {
        LambdaQueryWrapper<ProjectCheckStandardScore> queryWrapper = new LambdaQueryWrapper<ProjectCheckStandardScore>()
                .between(ProjectCheckStandardScore::getCreateTime, LocalDateTime.of(firstDayOfMonth, LocalTime.MIN), LocalDateTime.of(today, LocalTime.MAX));
        return iProjectCheckStandardScoreService.list(queryWrapper);
    }


    private List<CheckStandard> getCheckStandardList(String checkCriteriaId) {
        LambdaQueryWrapper<CheckStandard> queryWrapper = new LambdaQueryWrapper<CheckStandard>()
                .eq(CheckStandard::getCheckCriteriaId, checkCriteriaId);
        return iCheckStandardService.list(queryWrapper);
    }

    private List<CheckCriteria> getCheckCriteriaList(String checkModuleId) {
        LambdaQueryWrapper<CheckCriteria> queryWrapper = new LambdaQueryWrapper<CheckCriteria>()
                .eq(CheckCriteria::getCheckModuleId, checkModuleId);
        return iCheckCriteriaService.list(queryWrapper);
    }


    private List<CheckModule> getCheckModuleList(String checkTableId) {
        LambdaQueryWrapper<CheckModule> queryWrapper = new LambdaQueryWrapper<CheckModule>()
                .eq(CheckModule::getCheckTableId, checkTableId);
        return iCheckModuleService.list(queryWrapper);
    }
}
